CREATE DATABASE BD_SYS_ATENCIONES
GO
USE BD_SYS_ATENCIONES
GO

CREATE TABLE GRDetRequerimiento(
   iNumReq  INTEGER NOT NULL ,
   iCodMat  INTEGER NOT NULL ,
   nCanReq  NUMERIC (10,2) ,
   iCodUsu  INTEGER ,
   vNomTer  VARCHAR (20) ,
   dtFecAct DATETIME ,
   CONSTRAINT GRDetRequerimiento_PK PRIMARY KEY CLUSTERED (iCodMat, iNumReq)
   WITH(
      ALLOW_PAGE_LOCKS = ON ,
      ALLOW_ROW_LOCKS  = ON
      ) ON "default"
   )ON "default"
GO

CREATE TABLE GREstadoReq(
   iCodEstReq INTEGER NOT NULL ,
   vNomEstReq VARCHAR (100) NOT NULL,
   iCodUsu    INTEGER NOT NULL,
   vNomTer    VARCHAR (20) NOT NULL,
   dtFecAct   DATETIME NOT NULL,
   CONSTRAINT GREstadoReq_PK PRIMARY KEY CLUSTERED (iCodEstReq)
   WITH(
      ALLOW_PAGE_LOCKS = ON ,
      ALLOW_ROW_LOCKS  = ON
      ) ON "default"
   ) ON "default"
GO

CREATE TABLE GRMaeRequerimiento  (
   iNumReq       INTEGER identity(1,1) NOT NULL ,
   iCodUsuReg    integer        Not NUll,
   dtFecReq      DATETIME       NOT NULL ,
   vHorReq       VARCHAR (8)    NOT NULL ,
   iCodEstReq    INTEGER        NOT NULL ,
   vObsReq       VARCHAR (1000) ,
   bAprobado     BIT ,
   iCodUsuApro   INTEGER ,
   dtFecApro     DATETIME ,
   iCodUsuAte    INTEGER ,
   dtFecAte      DATETIME ,
   vObsAte       VARCHAR (1000) ,
   iCodUsuA      INTEGER        NOT NULL ,
   vNomTer       VARCHAR (20)   NOT NULL ,
   dtFecAct      DATETIME       NOT NULL ,
   CONSTRAINT GRMaeRequerimiento_PK PRIMARY KEY CLUSTERED (iNumReq)
   WITH(
      ALLOW_PAGE_LOCKS = ON ,
      ALLOW_ROW_LOCKS  = ON
      ) ON "default"
   ) ON "default"
  GO

CREATE TABLE GRMovRequerimiento(
   iCorMovReq INTEGER identity(1,1) NOT NULL ,
   iCodUOr    INTEGER  NOT NULL ,
   iCodUsu    INTEGER  NOT NULL ,
   iNumReq    INTEGER  NOT NULL ,
   iCodEstReq integer  Not NUll,
   dtFecMov   DATETIME NOT NULL ,
   vObsMov    VARCHAR  (1000) ,
   iCodUsuA   INTEGER  NOT NULL ,
   vNomTer    VARCHAR  (20) NOT NULL ,
   dtFecAct   DATETIME NOT NULL     
   CONSTRAINT GRMovRequerimiento_PK PRIMARY KEY CLUSTERED (iCorMovReq)
   WITH(
      ALLOW_PAGE_LOCKS = ON ,
      ALLOW_ROW_LOCKS  = ON
      ) ON "default"  
   ) ON "default"  
  GO

ALTER TABLE GRDetRequerimiento
   ADD CONSTRAINT GRDetRequerimiento_GRMaeRequerimiento_FK 
   FOREIGN KEY (iNumReq) REFERENCES GRMaeRequerimiento(iNumReq)
ON DELETE  NO ACTION 
ON UPDATE NO ACTION
GO

ALTER TABLE GRMaeRequerimiento
   ADD CONSTRAINT GRMaeRequerimiento_GREstadoReq_FK 
   FOREIGN KEY(iCodEstReq) REFERENCES GREstadoReq(iCodEstReq)
ON DELETE NO ACTION 
ON UPDATE NO ACTION
GO

ALTER TABLE GRMovRequerimiento
   ADD CONSTRAINT GRMovRequerimiento_GRMaeRequerimiento_FK 
   FOREIGN KEY (iNumReq) REFERENCES GRMaeRequerimiento(iNumReq)
ON DELETE NO ACTION 
ON UPDATE NO ACTION

ALTER TABLE GRMovRequerimiento
   ADD CONSTRAINT GRMovRequerimiento_GREstadoReq_FK 
   FOREIGN KEY(iCodEstReq) REFERENCES GREstadoReq(iCodEstReq)
ON DELETE NO ACTION 
ON UPDATE NO ACTION

insert into GREstadoReq values(1,'ACTIVO','1','ESTACION 1','2014-01-22 02:48:49')
insert into GREstadoReq values(2,'ASIGNADO','1','ESTACION 1','2014-01-22 02:48:49')
insert into GREstadoReq values(3,'ANULADO','3','ESTACION 3','2014-01-22 02:48:49')
insert into GREstadoReq values(4,'ATENDIDO','3','ESTACION 3','2014-01-22 02:48:49')

Insert Into GRMaeRequerimiento 
   Values(1, GETDATE(), Convert(varchar(8), getdate(),108), 1, 'Registrado - Pruebas', 0, Null, Null, Null, Null, Null, 1, 'PC01', GETDATE())
Insert Into GRMaeRequerimiento 
   Values(1, GETDATE(), Convert(varchar(8), getdate(),108), 3, 'Pruebas', 0, Null, Null, Null, Null, Null, 1, 'PC01', GETDATE())
   